package com.tjtt.gwm.dao.impl;

import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.tjtt.gwm.dao.JiancerenwuTongjiDao;
import com.tjtt.tdm.base.BaseHibernateDao;
import com.tjtt.tdm.base.PageResult;

public class JiancerenwuTongjiDaoImpl extends BaseHibernateDao implements JiancerenwuTongjiDao {

	/*
	 * 查询检测任务统计列表
	 * wxj
	 */
	public PageResult queryJiancerenwuTJList(Map filterMap){
		StringBuffer countString = new StringBuffer();
		countString.append("select count(*) from (select DISTINCT t.t_deptid,"+
                "t.t_deptname,"+
                "NVL((select sum(count(DISTINCT t1.t_itemid))"+
                    "  from GWM_ITEM_SAMPLE t1"+
                   "  where t1.t_deptid = t.t_deptid");
		if(null!=filterMap && !filterMap.isEmpty()){
			if(null!=filterMap.get("addtime1") && !"".equals(filterMap.get("addtime1"))){
				countString.append(" and t1.addtime > '"+filterMap.get("addtime1")+"'");
			}
			if(null!=filterMap.get("addtime2") && !"".equals(filterMap.get("addtime2"))){
				countString.append(" and t1.addtime < '"+filterMap.get("addtime2")+"'");
			}
		}
        countString.append("   group by t1.t_taskid),0) renwuzongshu, /*接受的任务数*/"+
                "NVL((select sum(count(DISTINCT t1.t_itemid))"+
                    "  from GWM_ITEM_SAMPLE t1, FLOW_TASK f"+
                   "  where t1.t_deptid = t.t_deptid"+
                    "   and t1.t_taskid = f.id"+
                    "   and f.flow_state_id = 'end'");
        if(null!=filterMap && !filterMap.isEmpty()){
        	if(null!=filterMap.get("addtime1") && !"".equals(filterMap.get("addtime1"))){
				countString.append(" and t1.addtime > '"+filterMap.get("addtime1")+"'");
			}
			if(null!=filterMap.get("addtime2") && !"".equals(filterMap.get("addtime2"))){
				countString.append(" and t1.addtime < '"+filterMap.get("addtime2")+"'");
			}
		}
        countString.append("   group by t1.t_taskid),0) yiwanchengshu, /*已完成的任务数*/"+
                 " NVL((select sum(count(DISTINCT t1.t_itemid))"+
                  "    from GWM_ITEM_SAMPLE t1, FLOW_TASK f"+
                  "   where t1.t_deptid = t.t_deptid"+
                 "      and t1.t_taskid = f.id"+
                  "     and f.flow_state_id != 'end'");
      if(null!=filterMap && !filterMap.isEmpty()){
    	  if(null!=filterMap.get("addtime1") && !"".equals(filterMap.get("addtime1"))){
				countString.append(" and t1.addtime > '"+filterMap.get("addtime1")+"'");
			}
			if(null!=filterMap.get("addtime2") && !"".equals(filterMap.get("addtime2"))){
				countString.append(" and t1.addtime < '"+filterMap.get("addtime2")+"'");
			}
	  }
       countString.append("   group by t1.t_taskid),0) weiwanchengshu, /*未完成的任务数*/"+
               " NVL((select sum(count(DISTINCT t1.t_itemid))"+
                 "     from GWM_ITEM_SAMPLE t1, FLOW_TASK f"+
                  "   where t1.t_deptid = t.t_deptid"+
                  "     and t1.t_taskid = f.id"+
                  "     and f.flow_state_id != 'end'");
       if(null!=filterMap && !filterMap.isEmpty()){
    	   if(null!=filterMap.get("addtime1") && !"".equals(filterMap.get("addtime1"))){
				countString.append(" and t1.addtime > '"+filterMap.get("addtime1")+"'");
			}
			if(null!=filterMap.get("addtime2") && !"".equals(filterMap.get("addtime2"))){
				countString.append(" and t1.addtime < '"+filterMap.get("addtime2")+"'");
			}
		}
       countString.append("     and f.state = '3'"+
                  "   group by t1.t_taskid),0) quxiaorenwushu /*取消的任务数*/"+
 " from GWM_ITEM_SAMPLE t)  where 1 = 1 ");
       
		StringBuffer queryString = new StringBuffer();	
		queryString.append("select DISTINCT t.t_deptid,"+
                "t.t_deptname,"+
                "NVL((select sum(count(DISTINCT t1.t_itemid))"+
                    "  from GWM_ITEM_SAMPLE t1"+
                   "  where t1.t_deptid = t.t_deptid");
		if(null!=filterMap && !filterMap.isEmpty()){
			if(null!=filterMap.get("addtime1") && !"".equals(filterMap.get("addtime1"))){
				queryString.append(" and t1.addtime > '"+filterMap.get("addtime1")+"'");
			}
			if(null!=filterMap.get("addtime2") && !"".equals(filterMap.get("addtime2"))){
				queryString.append(" and t1.addtime < '"+filterMap.get("addtime2")+"'");
			}
		}
        queryString.append("   group by t1.t_taskid),0) renwuzongshu, /*接受的任务数*/"+
                "NVL((select sum(count(DISTINCT t1.t_itemid))"+
                    "  from GWM_ITEM_SAMPLE t1, FLOW_TASK f"+
                   "  where t1.t_deptid = t.t_deptid"+
                    "   and t1.t_taskid = f.id"+
                    "   and f.flow_state_id = 'end'");
        if(null!=filterMap && !filterMap.isEmpty()){
        	if(null!=filterMap.get("addtime1") && !"".equals(filterMap.get("addtime1"))){
				queryString.append(" and t1.addtime > '"+filterMap.get("addtime1")+"'");
			}
			if(null!=filterMap.get("addtime2") && !"".equals(filterMap.get("addtime2"))){
				queryString.append(" and t1.addtime < '"+filterMap.get("addtime2")+"'");
			}
		}
       queryString.append("   group by t1.t_taskid),0) yiwanchengshu, /*已完成的任务数*/"+
              " NVL((select sum(count(DISTINCT t1.t_itemid))"+
                  "    from GWM_ITEM_SAMPLE t1, FLOW_TASK f"+
                  "   where t1.t_deptid = t.t_deptid"+
                 "      and t1.t_taskid = f.id"+
                  "     and f.flow_state_id != 'end'");
       if(null!=filterMap && !filterMap.isEmpty()){
    	   if(null!=filterMap.get("addtime1") && !"".equals(filterMap.get("addtime1"))){
				queryString.append(" and t1.addtime > '"+filterMap.get("addtime1")+"'");
			}
			if(null!=filterMap.get("addtime2") && !"".equals(filterMap.get("addtime2"))){
				queryString.append(" and t1.addtime < '"+filterMap.get("addtime2")+"'");
			}
		}
       queryString.append("   group by t1.t_taskid),0) weiwanchengshu, /*未完成的任务数*/"+
               " NVL((select sum(count(DISTINCT t1.t_itemid))"+
                 "     from GWM_ITEM_SAMPLE t1, FLOW_TASK f"+
                  "   where t1.t_deptid = t.t_deptid"+
                  "     and t1.t_taskid = f.id"+
                  "     and f.flow_state_id != 'end'");
       if(null!=filterMap && !filterMap.isEmpty()){
    	   if(null!=filterMap.get("addtime1") && !"".equals(filterMap.get("addtime1"))){
				queryString.append(" and t1.addtime > '"+filterMap.get("addtime1")+"'");
			}
			if(null!=filterMap.get("addtime2") && !"".equals(filterMap.get("addtime2"))){
				queryString.append(" and t1.addtime < '"+filterMap.get("addtime2")+"'");
			}
		}
      queryString.append("     and f.state = '3'"+
                  "   group by t1.t_taskid),0) quxiaorenwushu /*取消的任务数*/"+
      " from GWM_ITEM_SAMPLE t  where 1 = 1 ");
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);
	}
}
